<?php

defined('SYSPATH') or die('No direct script access.');

class Model_Book extends Model_Database {

	public function get_data($table, $user = NULL, $order = NULL, $item = FALSE) {
		DB::query(Database::SELECT, "SELECT @i:=0;")->execute();
		$data = DB::select(array(DB::expr('@i:=@i+1'), 'num'), $table . '.*')
				->from($table);
		if ($table == 'orders' AND is_null($user)) {
			$data->where('complete', '=', 'готов');
		}
		if ($item == TRUE AND is_null($user)) {
			return $data->where('id', '=', $order)
							->cached(1)
							->limit(1)
							->execute()
							->current();
		}
		elseif (!is_null($user)) {
			$data->where('userid', '=', $user);
		}
		elseif (!is_null($order)) {
			$data->where('orderid', '=', $order);
		}
		return $data->execute()
						->as_array();
		//echo Database::instance()->last_query;
	}

	public function get_order($order = 0) {
		return DB::select()
						->from('orders')
						->where('id', '=', $order)
						->cached(1)
						->limit(1)
						->execute()
						->current();
	}

	public function set_order($data, $order = NULL) {
		if (empty($order)) {
			$query = DB::insert('orders', array_keys($data))
					->values($data)
					->execute();
		}
		else {
			$query = DB::update('orders')
					->set($data)
					->where('id', '=', $order)
					->execute();
		}
	}

	public function update($table, $where, $data) {
		$query = DB::update($table)
				->set($data)
				->where(key($where), '=', current($where))
				->execute();
		return TRUE;
	}

	public function delete($table, $data) {
		DB::delete($table)
				->where(key($data), '=', current($data))
				->execute();
		//echo Database::instance()->last_query;
	}

	public function into_files($data) {
		DB::insert('files', array_keys($data))
				->values($data)
				->execute();
	}

	public function into_stack($data) {
		DB::insert('emailstack', array('data'))
				->values(array($data))
				->execute();
	}

	public function from_stack() {
		return DB::select()
						->from('emailstack')
						->execute()
						->as_array();
	}

	public function enum_values($table, $column) {
		$sql = "SELECT COLUMN_TYPE
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = '" . Database::instance()->table_prefix() . $table . "' AND COLUMN_NAME = '" . $column . "'";
		$row = DB::query(Database::SELECT, $sql)
				->cached(3600)
				->execute()
				->current();
		$row = explode(",", str_replace("'", "", substr($row['COLUMN_TYPE'], 5, (strlen($row['COLUMN_TYPE']) - 6))));
		foreach ($row as $id => $line) {
			$rows[$line] = $line;
		}
		return $rows;
	}

	public function get_config($key, $cache = TRUE) {
		$result = DB::select();
		$result->from('config')
				->where('key', '=', $key)
				->limit(1);
		if ($cache == TRUE) {
			$result->cached(60, $cache);
		}
		else {
			$result->cached(null, $cache);
		}
		return $result->execute()->get('value');
	}

	public function set_config($key, $value) {
		$key = Database::instance()->quote($key);
		$value = Database::instance()->quote($value);
		$query = "INSERT INTO `" . Database::instance()->table_prefix() . "config` VALUES ({$key}, {$value})
			ON DUPLICATE KEY UPDATE `value` = '{$value}'";
		Database::instance()->query(NULL, $query);
		return $value;
	}

	public function recalculate($orderid = 0) {
		$exchange = $this->get_config('exchange', TRUE);
		$sum = DB::select(
						array(DB::expr('CONCAT_WS(\'/\',COUNT(*),SUM(`quantity`))'), 'itemscount'), array(DB::expr('SUM(`quantity`*`cost`+POW(`quantity`,0)*`delivery`)*1.2*' . $exchange . '+100*' . $exchange), 'cost')
				)
				->from('items')
				->where('orderid', '=', $orderid)
				->and_where('status', '!=', 'нет в наличии')
				->execute()
				->current();
		//echo Database::instance()->last_query;
		DB::update('orders')
				->set($sum)
				->where('id', '=', $orderid)
				->execute();
	}

	public function get_userinfo($orderid = 0) {
		if (is_array($orderid)) {
			return $a = DB::select('id', 'email', 'username')
					->from('users')
					->where('id', '=', $orderid[0])
					->cached(3600)
					->execute()
					->current();
		}
		else {
			$order = $this->get_order($orderid);
			if (!empty($order)) {
				return $a = DB::select('id', 'email', 'username')
						->from('users')
						->where('id', '=', $order['userid'])
						->cached(3600)
						->execute()
						->current();
				//echo Database::instance()->last_query;
			}
		}
		return FALSE;
	}

	public function get_balance($userid = NULL){
		if (empty($userid)){
			$balances = DB::select(
					'id','email','username','balance',array(DB::expr('SUM(`value`)'), 'sum')
				)
				->from('users')
				->join('user_balance_log', 'LEFT')
				->on('user_balance_log.userid', '=', 'users.id')
				->group_by('id')
				//->cached(300)
				->execute()
				->as_array();
		}
		else {
			$balances = DB::select(
					'id','email','username','balance',array(DB::expr('SUM(`value`)'), 'sum')
				)
				->from('users')
				->join('user_balance_log', 'LEFT')
				->on('user_balance_log.userid', '=', 'users.id')
				->where('id', '=', $userid)
				//->cached(300)
				->execute()
				->as_array();
		}
		return $balances;
	}

	public function change_balance($data){
		DB::insert('user_balance_log', array_keys($data))
			->values($data)
			->execute();
		$data = $this->get_balance($data['userid']);
		$data = current($data);
		$this->update('users', array('id'=>$data['id']), array('balance'=>$data['sum']));
	}

	public function get_balance_log($userid){
		return DB::select('time','value','orderid')
			->from('user_balance_log')
			->where('userid', '=', $userid)
			->order_by('time','ASC')
			//->cached(300)
			->execute()
			->as_array();
	}

}
